<#assign objects = doc.objects>
package ${objects.package}.service;

import java.io.File;
import java.io.FileFilter;
import java.io.InputStream;
import java.io.IOException;
import java.io.PrintWriter;

import java.sql.BatchUpdateException;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import redora.rdo.api.*;
import redora.rdo.exceptions.*;
import redora.rdo.impl.*;
import redora.rdo.configuration.rdo.model.RedoraConfiguration;
import redora.rdo.configuration.rdo.service.RedoraConfigurationService;
import redora.rdo.configuration.rdo.model.base.RedoraConfigurationBase.Status;
import ${objects.package}.service.base.*;

public class Upgrade extends ServiceBase {
	private Logger l = Logger.getLogger("${objects.package}.model.service.Upgrade");

	public final static String[] OBJECTS = {
<#list doc["/objects/object/name"] as modelname>
		"${modelname}"<#if modelname_has_next>,</#if>
</#list>
	};
	
	public final static String[] RELATION_TABLES = {
<#list doc["/objects/object/attributes/set[@multiplicity='n-to-m']"] as att>
	<#assign object = att?parent?parent>
	<@relation_table att=att />
		"${relation_table_name}"<#if att_has_next>,</#if>
</#list>
	};
	
	public Upgrade() throws ConnectException {
		super();
	}
	
	private final static String SCRIPT_SOURCE_DIR = "src" + File.separator + "main" + File.separator + "resources" + File.separator + "upgrade";
	
	public void upgradeTable(PrintWriter out) throws ConnectException, PersistException
		, CopyException, LazyException, BusinessRuleViolationException, IOException, SQLException, QueryException {
		l.log(Level.INFO, "Found " + OBJECTS.length + " model files to process");
<#list doc["/objects/object/name"] as modelname>
		${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
		${modelname?lower_case}Update.create();
		${modelname?lower_case}Update.close();
</#list>
		
		l.log(Level.INFO, "Finished running create table scripts");
		executeUpgradeScript();
		
		connect();
		boolean retVal = false;
		try {
			rs = con.getMetaData().getTables(null, null, null, null);
			while(rs.next()){
				retVal = false;
				for(String tableName : OBJECTS){
					if(rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)){
						retVal = true;
					}
				}
				for(String tableName : RELATION_TABLES){
					if(rs.getString("TABLE_NAME").equalsIgnoreCase(tableName)){
						retVal = true;
					}
				}
				if(!retVal){
					l.log(Level.WARNING, "table " + rs.getString("TABLE_NAME") + " is not existed in xml file but it is in database");
					if(out != null)
						out.println("table " + rs.getString("TABLE_NAME") + " is not existed in xml file but it is in database." + "<br>");
				}
			}
		} catch (SQLException e) {
			l.log(Level.SEVERE, "Failed to open meta data", e);
			throw new QueryException("Failed to open meta data", e);
		}
		
	}
	
	public void check(PrintWriter out) throws ConnectException, QueryException {
		<#list doc["/objects/object/name"] as modelname>
		${modelname?cap_first}TableUpdate ${modelname?lower_case}Update = new ${modelname?cap_first}TableUpdate();
		${modelname?lower_case}Update.check(out);
		${modelname?lower_case}Update.close(); 
		</#list>
		<#list doc["/objects/object/attributes/set[@multiplicity='n-to-m']"] as att>
			<#assign object = att?parent?parent>
			<@relation_table att=att />
		checkRelationTable("${relation_table_name}","${att.@class}","${object.name}",out);	
		</#list>
	}
	
	public void checkRelationTable(String tableName,String objectA,String objectB,PrintWriter out) throws ConnectException, QueryException {
		try {
			connect();
			rs = con.getMetaData().getColumns("", "", tableName, "");
			Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
			while (rs.next()) {
				Map<String, Object> list = new HashMap<String, Object>();
				list.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
				list.put("DATA_TYPE", rs.getInt("DATA_TYPE"));
				map.put(rs.getString("COLUMN_NAME"), list);
			}
			for (String key : map.keySet()) {
				String COLUMN_NAME = map.get(key).get("COLUMN_NAME").toString();
				if(!(COLUMN_NAME.equalsIgnoreCase("id") || COLUMN_NAME.equalsIgnoreCase(objectA + "Id") || COLUMN_NAME.equalsIgnoreCase(objectB + "Id")
					|| COLUMN_NAME.equalsIgnoreCase("creationDate") || COLUMN_NAME.equalsIgnoreCase("updateDate") || COLUMN_NAME.equalsIgnoreCase("roDeleted"))) {
					l.log(Level.WARNING, tableName + " does not have " + key
							+ " attribute but it exists in database");
					if (out != null)
						out.println(tableName + " does not have " + key
								+ " attribute but it exists in database." + "<br>");
				}
			}
			if (!map.containsKey("id")) {
				l.log(Level.WARNING,tableName + ".id does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".id does not existed or its sqltype does not match according to xml." + "<br>");
			}
			if (!map.containsKey(objectA + "Id")) {
				l.log(Level.WARNING,tableName + "." + objectA + "Id" + " does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + "." + objectA + "Id" + " does not existed or its sqltype does not match according to xml." + "<br>");		
			}
			if (!map.containsKey(objectB + "Id")) {
				l.log(Level.WARNING,tableName + "." + objectB + "Id" + " does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + "." + objectB + "Id" + " does not existed or its sqltype does not match according to xml." + "<br>");			
			}
			if (!map.containsKey("creationDate")) {
				l.log(Level.WARNING,tableName + ".creationDate does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".creationDate does not existed or its sqltype does not match according to xml." + "<br>");				
			}
			if (!map.containsKey("updateDate")) {
				l.log(Level.WARNING,tableName + ".updateDate does not existed or its sqltype does not match according to xml");
				if(out != null)
					out.println(tableName + ".updateDate does not existed or its sqltype does not match according to xml." + "<br>");
			}
		} catch (SQLException e) {
			l.log(Level.SEVERE, "Failed to execute checkRelationTable " + tableName, e);
			throw new QueryException("Failed to execute checkRelationTable " + tableName, e);
		}
	}
	
	public void executeUpgradeScript() throws ConnectException, PersistException, QueryException
		, CopyException, LazyException, BusinessRuleViolationException, IOException, SQLException {
		
		initDefaultScript();
		
		RedoraConfigurationService service = redora.rdo.configuration.rdo.service.ServiceFactory.redoraConfigurationService();		
        
        for (RedoraConfiguration conf : service.findByStatus(Status.New,FetchScope.Table)) {
			connect();
			s = con.createStatement();
			//con.setAutoCommit(false);
			for (String sql : loadSql(conf.getScriptName())) {
				s.addBatch(sql);
				l.log(Level.INFO, "Adding SQL statement: " + sql);
			}
			
			conf.setStatus(Status.Ready);
					
			try {
				l.log(Level.FINE, "Executing " + conf.getScriptName()); 
				s.executeBatch();
			} catch (BatchUpdateException e) {
				l.log(Level.SEVERE, "Failed to execute a statement in " + conf.getScriptName(), e);
				conf.setStatus(Status.Error);
				conf.setOutput(e.toString());
			}
			
			Set<BusinessRuleViolation> brViolations = service.persist(conf);
			if (!brViolations.isEmpty()) {
				String s = "One or more business rules have been violated: ";
				for (BusinessRuleViolation br : brViolations) {
					s += br.getPersistable().getClass().getName() + " id: " + br.getPersistable().getId() + " for rule: " + br.getBusinessRuleId();
				}
				throw new BusinessRuleViolationException(s);
			}        }
        
		service.close();
	}
	
	/**
	* Scans  the SCRIPT_SOURCE_DIR for new upgrade scripts. Adds them to RedoraConfiguration.
	*/
	public void initDefaultScript() throws ConnectException, PersistException, QueryException
		, CopyException, BusinessRuleViolationException {
		File dir = new File(SCRIPT_SOURCE_DIR);
		if (dir.exists()) {
			File[] files = dir.listFiles(new FileFilter() {
					public boolean accept(File pathname) {
						return pathname.getName().endsWith(".sql");
					}
				});
			if (files != null) {
				RedoraConfigurationService service = redora.rdo.configuration.rdo.service.ServiceFactory.redoraConfigurationService();
				for (int i = 0; i < files.length; i++) {
					if (service.findByScriptName(files[i].getName(),FetchScope.Table).isEmpty()) {
						RedoraConfiguration conf = new RedoraConfiguration();
						conf.setScriptName(files[i].getName());
						Set<BusinessRuleViolation> brViolations = service.persist(conf);
						if (!brViolations.isEmpty()) {
							String s = "One or more business rules have been violated: ";
							for (BusinessRuleViolation br : brViolations) {
								s += br.getPersistable().getClass().getName() + " id: " + br.getPersistable().getId() + " for rule: " + br.getBusinessRuleId();
							}
							throw new BusinessRuleViolationException(s);
						}
						l.log(Level.INFO, "Found new script file " + conf.getScriptName());
					} 
				}
				service.close();
			}
		}
	}
	
	/**
	* Locates the script file and makes a list of sql statements in the file.
	* Each SQL statement is supposed to end with ;.
	*
	* @return Empty or filled list with SQL statements.
	*/
	private List<String> loadSql(String sqlFile) throws IOException {
		List<String> retval = new ArrayList<String>();
		
		InputStream sqlFileIn = findUpgradeFile(sqlFile);
		StringBuffer sqlSb = new StringBuffer();
		byte[] buff = new byte[1024];
		int byteRead = 0;
		while ((byteRead = sqlFileIn.read(buff)) != -1) {
			sqlSb.append(new String(buff, 0, byteRead));
		}
		String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)|(;\\s*\\n)");
		for (int i = 0; i < sqlArr.length; i++) {
			String sql = sqlArr[i].replaceAll("--.*", "").trim();
			if (!sql.equals("")) {
				retval.add(sql);
			}
		}
		return retval;
	}
	
	public static void main(String args[])throws PersistenceException, BusinessRuleViolationException, IOException, SQLException {
		Upgrade upgrade = new Upgrade();
		upgrade.upgradeTable(null);
		upgrade.check(null);
		upgrade.close();
	}
}
